-- oracle 12C  CDB PDB
-- O1 PDB

-- alter session set container = O1;
-- alter session set container = cdb$root;

-- sqlplus root/root@O1;

select * from DBA_DATA_FILES;





-- 创建表空间示例如下：
CREATE TABLESPACE "WMS"
    LOGGING
    DATAFILE 'C:\1.installed\oracle\db\base\oradata/WMS.DBF' SIZE 10M
AUTOEXTEND ON NEXT 5M MAXSIZE 50M;

/



create user root identified by root;
create user C##WMS identified by C##WMS;
--alter user C##WMS identified by C##WMS;
grant dba to C##WMS container = all;

alter user C##WMS quota unlimited on WMS;


grant connect,resource,dba to root;
grant connect,resource,dba to C##ZXWDZ;
grant connect,resource,dba to root container = all;
grant connect,resource,dba to C##ZXWDZ; container = all;


-- revoke dba from root;


create user c##root identified by root;
alter user c##root identified by c##root;
grant dba to c##root container = all;

-- 创建表空间
create tablespace GDB datafile 'C:\1.installed\oracle\db\base\oradata\GDB.dbf' size 20m;
drop table if existsSPACE GDB INCLUDING CONTENTS AND DATAFILES;



-- oracle数据库清理缓存
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH GLOBAL CONTEXT;



--查询表空间信息
select *
--tablespace_name,sum(bytes)/1024/1024
from dba_data_files
where tablespace_name = 'ZZXZ'
--group by tablespace_name
;
--修改表空间大小
ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\ORCL\ZZXZ.DBF' RESIZE 30G;

--查询空闲表空间信息
select tablespace_name,sum(bytes)/1024/1024 from DBA_FREE_SPACE
group by tablespace_name;


--设置表空间自动增长 , 每次自动增长200m，最大不超过1G
--ALTER DATABASE DATAFILE '' AUTOEXTEND ON NEXT 200M MAXSIZE 1024M;



D:\oracle\product\12.2.0\dbhome_1\bin


 --11.2.0.1.0

impdp MES/MES@orcl directory=DATA_PUMP_DIR dumpfile=XCXJDZADMIN_expdp1.dmp version=11.2.0.1.0 full=y table_exists_action=replace remap_tablespace=XCXJDZ_MES:MES remap_schema=XCXJDZADMIN:MES logfile=mes-impdb.log cluster=n


impdp ZZXZ/12345678@orcl directory=DATA_PUMP_DIR dumpfile=XCXJDZADMIN_expdp1.dmp table_exists_action=replace remap_schema=XCXJDZADMIN:ZZXZ



--字符串拆分
select REGEXP_SUBSTR(${IDS},'[^,]+',1,LEVEL)
from dual
CONNECT BY
    LEVEL <= LENGTH(${IDS})-LENGTH(REPLACE(${IDS},','))+1
;

